/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import utils.Handle;

/**
 *
 * @author jamie
 */
public class MakeClusterIntersectionsTable {

    public MakeClusterIntersectionsTable() {

        Handle h = Handle.getConnectedHandle("monkeys");
        String sql = "drop table if exists clusterIntersections";
        h.runSQLupdate(sql);
        sql = "create table clusterIntersections (id int primary key, monkeyType_a char(1) not null, cellType_a char(1) not null, clusterId_a int(2) not null, size_a int(4) not null, " +
                "monkeyType_b char(1) not null, cellType_b char(1) not null, clusterId_b int(2) not null, size_b int(4) not null, " +
                "intersection int(4) not null, representation int(1) not null, mcc double not null, pValue double not null, fdr double not null)";
        h.runSQLupdate(sql);
        sql = "create index monkeytype_a_index on clusterIntersections(monkeytype_a)";
        h.runSQLupdate(sql);
        sql = "create index monkeytype_b_index on clusterIntersections(monkeytype_b)";
        h.runSQLupdate(sql);
        sql = "create index celltype_a_index on clusterIntersections(celltype_a)";
        h.runSQLupdate(sql);
        sql = "create index celltype_b_index on clusterIntersections(celltype_b)";
        h.runSQLupdate(sql);
        sql = "create index clusterId_a_index on clusterIntersections(clusterId_a)";
        h.runSQLupdate(sql);
        sql = "create index clusterId_b_index on clusterIntersections(clusterId_b)";
        h.runSQLupdate(sql);


        sql = "drop table if exists clusterIntersectionData";
        h.runSQLupdate(sql);
        sql = "create table clusterIntersectionData(id int not null primary key auto_increment, intersectionId int not null, probeId int not null)";
        h.runSQLupdate(sql);
        sql = "create index intersectionId_index on clusterIntersectionData(intersectionId)";
        h.runSQLupdate(sql);
        sql = "create index probeId_index on clusterIntersectionData(probeId)";
        h.runSQLupdate(sql);
    }



}
